import 'dart:async';

import 'package:amap_map_fluttify/amap_map_fluttify.dart';
import 'package:detect/entity/entity.dart';
import 'package:detect/utils/extensions.dart';
import 'package:detect/utils/global.dart';
import 'package:detect/utils/option_util.dart';
import 'package:detect/utils/toast_util.dart';
import 'package:flutter/foundation.dart';
import 'package:log4f/log4f.dart';
import 'package:sqflite/sqflite.dart';

class DBCreateTable {
  final String tableName;
  final List<String> fields;
  final bool dropOnCreate;

  DBCreateTable(this.tableName, this.fields, [this.dropOnCreate = false]);
}

class DBUtil {
  static final Map<String, DBUtil> _caches = {};
  final String name;
  final Database db;

  DBUtil._(this.name, this.db);

  factory DBUtil(String name) {
    if (_caches.containsKey(name)) {
      return _caches[name]!;
    } else {
      throw Exception("数据库$name不存在");
    }
  }

  static Future<DBUtil> initDB(String name, List<DBCreateTable> initTables) async {
    Completer<DBUtil> completer = Completer<DBUtil>();
    if (!_caches.containsKey(name)) {
      String path = await databaseFactory.getDatabasesPath();
      await databaseFactory.openDatabase(
        "$path/$name.db",
        options: OpenDatabaseOptions(
          version: 1,
          onConfigure: (db) async {
            // await db.execute("DROP TABLE IF EXISTS ${name}_GeneralTaskBest");
            await db.execute("PRAGMA foreign_keys = ON;");
            for (DBCreateTable table in initTables) {
              if (kDebugMode && table.dropOnCreate) await db.execute("DROP TABLE IF EXISTS ${name}_${table.tableName};");
              String sql = "CREATE TABLE IF NOT EXISTS ${name}_${table.tableName}(${table.fields.join(",")});";
              await db.execute(sql);
              // debugPrint("创建数据表 => $sql");
              Log4f.i(msg: sql, tag: "创建数据表");
            }
            _caches[name] = DBUtil._(name, db);
            completer.complete(_caches[name]);
          },
        ),
      );
    }
    return completer.future;
  }

  static Future<void> closeAll() async {
    for (DBUtil dbUtil in _caches.values) {
      await dbUtil.close();
    }
  }

  Future<int> insertBatch(String tableName, List<Map<String, dynamic>> values, [List<String> ignoreColumns = const <String>['id']]) async {
    // int rows = 0;
    // for (Map<String, dynamic> item in values) {
    //   rows += await insert(tableName, item, ignoreColumns);
    // }
    for (Map<String, dynamic> json in values) {
      json.removeWhere((key, value) => ignoreColumns.contains(key));
    }
    return db.transaction((txn) => txn.rawInsert(
        "INSERT INTO ${name}_$tableName(${values.first.keys.map((e) => "`$e`").join(',')}) VALUES ${values.map((e) => "(${e.values.map((e) => e is String ? "'$e'" : e).join(',')})").join(',')}"));
  }

  Future<int> insert(String tableName, Map<String, dynamic> value, [List<String> ignoreColumns = const <String>['id']]) async {
    value.removeWhere((key, value) => ignoreColumns.contains(key));
    return db.transaction<int>((txn) => txn.insert("${name}_$tableName", value));
  }

  Future<int> delete(String tableName, [String? where, List<dynamic>? args]) => db.transaction((txn) => txn.delete("${name}_$tableName", where: where, whereArgs: args));

  Future<int> update(String tableName, Map<String, dynamic> values, [String? where, List<dynamic>? args]) {
    values.removeWhere((key, value) => 'id' == key || value == null);
    return db.transaction((txn) => txn.update("${name}_$tableName", values, where: where, whereArgs: args));
  }

  Future<List<Map<String, dynamic>>> select(String tableName, {List<String>? columns, String? where, List<dynamic>? args, String? groupBy, String? having, String? orderBy, int? limit, int? offset}) =>
      db.query("${name}_$tableName", columns: columns, where: where, whereArgs: args, groupBy: groupBy, having: having, orderBy: orderBy, limit: limit, offset: offset);

  Future<int> length(String tableName, {List<String>? columns, String? where, List<dynamic>? args}) async => (await select(tableName, columns: columns, where: where, args: args)).length;

  Future<void> close() async {
    await db.close();
    _caches.remove(name);
  }
}

abstract class DBApi {
  final DBUtil db = DBUtil("DB");
  final String name;

  DBApi(this.name);

  String tableName([String? name]) => "${db.name}_${name ?? this.name}";

  static RadioApi get radio => RadioApi("Radio");

  static DisabledMHzApi get disabledMHz => DisabledMHzApi("DisabledMHz");

  static DetectTaskApi get detectTask => DetectTaskApi("DetectTask");

  static DetectTaskBestApi get detectTaskBest => DetectTaskBestApi("DetectTaskBest");

  static GeneralTaskHoursApi get generalTaskHours => GeneralTaskHoursApi("GeneralTaskHours");

  static GeneralTaskResultApi get generalTaskResult => GeneralTaskResultApi("GeneralTaskResult");

  static PickTaskHoursApi get pickTaskHours => PickTaskHoursApi("PickTaskHours");

  static PickTaskResultApi get pickTaskResult => PickTaskResultApi("PickTaskResult");

  static PickTaskResultPackageApi get pickTaskResultPackage => PickTaskResultPackageApi("PickTaskResultPackage");

  static ChatApi get chat => ChatApi("Chat");
}

class RadioApi extends DBApi {
  RadioApi(super.name);

  Future<RadioEntity?> insert(RadioEntity entity) async {
    try {
      if (await db.insert(name, entity.toJson(), []) >= 1) {
        List<Map<String, dynamic>> result = await db.select(name, where: "`name` = ?", args: [entity.name]);
        return result.isEmpty ? null : RadioEntity.fromJson(result.single);
      } else {
        return null;
      }
    } on DatabaseException catch (e) {
      if (e.isDuplicateColumnError('name') || e.isUniqueConstraintError('${tableName()}.name')) {
        ToastUtil.error("台站名称重复");
      } else if (e.isDuplicateColumnError('code') || e.isUniqueConstraintError('${tableName()}.code')) {
        ToastUtil.error("识别码重复");
      } else {
        ToastUtil.error("数据创建异常");
      }
      rethrow;
    }
  }

  Future<int> delete(int id) async {
    try {
      return await db.delete(name, "`id` = ?", [id]);
    } on DatabaseException catch (e) {
      if (e.isForeignKeyConstraintError()) {
        ToastUtil.error("该项数据已经使用，请先删除与之关联的数据！");
      } else {
        ToastUtil.error("删除数据异常");
      }
      rethrow;
    }
  }

  Future<RadioEntity?> update(RadioEntity entity) async {
    try {
      if (await db.update(name, entity.toJson(), "`id` = ?", [entity.id]) >= 1) {
        List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [entity.id]);
        return result.isEmpty ? null : RadioEntity.fromJson(result.single);
      } else {
        return null;
      }
    } on DatabaseException catch (e) {
      if (e.isDuplicateColumnError('name') || e.isUniqueConstraintError('${tableName()}.name')) {
        ToastUtil.error("台站名称重复");
      } else if (e.isDuplicateColumnError('code') || e.isUniqueConstraintError('${tableName()}.code')) {
        ToastUtil.error("识别码重复");
      } else {
        ToastUtil.error("数据创建异常");
      }
      rethrow;
    }
  }

  Future<RadioEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : RadioEntity.fromJson(result.single);
  }

  Future<bool> contain(int id) async => (await db.select(name, columns: ["`id`"], where: "`id` = ?", args: [id])).isNotEmpty;

  Future<List<RadioEntity>> list([String? where, List<dynamic>? args]) async => (await db.select(name, orderBy: "`id` ASC", where: where, args: args)).map((e) => RadioEntity.fromJson(e)).toList();

  Future<RadioEntity?> localRadio(int localId) async {
    List<RadioEntity> result = (await DBUtil("DB").select("Radio", where: "id = ?", args: [localId])).map<RadioEntity>((e) => RadioEntity.fromJson(e)).toList();
    return result.isEmpty ? null : result.single;
  }
}

class DisabledMHzApi extends DBApi {
  DisabledMHzApi(super.name);

  Future<DisabledMHzEntity?> insert(DisabledMHzEntity entity) async {
    if (await db.insert(name, entity.toJson()) >= 1) {
      List<Map<String, dynamic>> result = await db.select(name, orderBy: "`id` DESC", limit: 1);
      return result.isEmpty ? null : DisabledMHzEntity.fromJson(result.single);
    } else {
      return null;
    }
  }

  Future<int> delete(int id) async {
    try {
      return await db.delete(name, "`id` = ?", [id]);
    } on DatabaseException catch (e) {
      if (e.isForeignKeyConstraintError()) {
        ToastUtil.error("该项数据已经使用，请先删除与之关联的数据！");
      } else {
        ToastUtil.error("删除数据异常");
      }
      rethrow;
    }
  }

  Future<int> update(DisabledMHzEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<DisabledMHzEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : DisabledMHzEntity.fromJson(result.single);
  }

  Future<List<DisabledMHzEntity>> list([String? where, List<dynamic>? args]) async =>
      (await db.select(name, orderBy: "`id` ASC", where: where, args: args)).map((e) => DisabledMHzEntity.fromJson(e)).toList();
}

class DetectTaskApi extends DBApi {
  DetectTaskApi(super.name);

  Future<DetectTaskEntity?> insert(DetectTaskEntity entity) async {
    try {
      if (await db.insert(name, entity.toJson()) >= 1) {
        List<Map<String, dynamic>> result = await db.select(name, where: "`name` = ?", args: [entity.name]);
        return result.isEmpty ? null : DetectTaskEntity.fromJson(result.single);
      } else {
        return null;
      }
    } on DatabaseException catch (e) {
      if (e.isDuplicateColumnError('name') || e.isUniqueConstraintError('${tableName()}.name')) {
        ToastUtil.error("方案名称重复");
      } else {
        ToastUtil.error("数据创建异常");
      }
      rethrow;
    }
  }

  Future<int> delete(int id) => db.delete(name, "`id` = ?", [id]);

  Future<int> update(DetectTaskEntity entity) async {
    try {
      return await db.update(name, entity.toJson(), "`id` = ?", [entity.id]);
    } on DatabaseException catch (e) {
      if (e.isDuplicateColumnError('name') || e.isUniqueConstraintError('${tableName()}.name')) {
        ToastUtil.error("方案名称重复");
      } else {
        ToastUtil.error("数据更新异常");
      }
      rethrow;
    }
  }

  Future<int> state(int id, int state) => db.update(name, {"state": state}, "`id` = ?", [id]);

  Future<DetectTaskEntity?> select(int id) async {
    String sql = """
    SELECT a.`id`,a.`radio_id`,b.`name` `radio_name`,a.`type`,a.`role`,a.`name`,a.`time`,a.`disabled_mhz_id`,c.`values` `disabled_mhz_values`,a.`state`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("Radio")} b ON b.`id`=a.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} c ON c.`id`=a.`disabled_mhz_id`
    WHERE a.`id`=$id
    """;
    List<Map<String, dynamic>> result = await db.db.rawQuery(sql);
    return result.isEmpty ? null : DetectTaskEntity.fromJson(result.single);
  }

  Future<List<DetectTaskEntity>> list(TaskType type, [String? name, DateTime? startTime, DateTime? endTime]) async {
    String sql = """
    SELECT a.`id`,a.`radio_id`,b.`name` `radio_name`,a.`type`,a.`role`,a.`name`,a.`time`,a.`disabled_mhz_id`,c.`values` `disabled_mhz_values`,a.`state`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("Radio")} b ON b.`id`=a.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} c ON c.`id`=a.`disabled_mhz_id`
    """;
    List<String> conditions = ["a.`type`='${type.name}'"];
    if (name != null && name.isNotEmpty) conditions.add("a.`name` LIKE '%$name%'");
    if (startTime != null && endTime != null) {
      String? startTimeFormat = Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)), endTimeFormat = Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59));
      if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
        conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
      }
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`time` DESC";
    return (await db.db.rawQuery(sql)).map((e) => DetectTaskEntity.fromJson(e)).toList();
  }
}

class DetectTaskBestApi extends DBApi {
  DetectTaskBestApi(super.name);

  Future<String?> insertGeneral(int radioId, [DateTime? startTime, DateTime? endTime]) async {
    String? startTimeFormat = startTime == null ? null : Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)),
        endTimeFormat = endTime == null ? null : Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59)),
        sql = """
    SELECT a.`id` FROM ${tableName("GeneralTaskResult")} a
    INNER JOIN ${tableName("DetectTask")} b ON b.`id`=a.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=b.`radio_id`
    """;
    List<String> conditions = [];
    conditions.add("STRFTIME('%H',a.`time`)=?");
    conditions.add("b.`radio_id`=$radioId");
    if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
      conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`signal` DESC LIMIT 10";
    List<int> resultIds = [];
    List<String> hourList = List<String>.generate(24, (index) => index >= 10 ? '$index' : '0$index');
    for (String hour in hourList) {
      resultIds.addAll((await db.db.rawQuery(sql, [hour])).map((e) => int.tryParse(e['id']?.toString() ?? '')).toList().whereNotNull());
    }
    resultIds = resultIds.distinct((result, current) => result.contains(current));
    if (resultIds.isEmpty) {
      return null;
    } else {
      DateTime now = DateTime.now();
      String name = "G${now.millisecondsSinceEpoch}", time = Global.parseTime(now)!;
      await db.insert(this.name, DetectTaskBestEntity(TaskType.general.name, name, radioId, startTimeFormat, endTimeFormat, time, resultIds.join(',')).toJson());
      return name;
    }
  }

  Future<String?> insertPick(int radioId, [DateTime? startTime, DateTime? endTime]) async {
    String? startTimeFormat = startTime == null ? null : Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)),
        endTimeFormat = endTime == null ? null : Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59)),
        sql = """
    SELECT a.`id` FROM ${tableName("PickTaskResult")} a
    INNER JOIN ${tableName("DetectTask")} b ON b.`id`=a.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=b.`radio_id`
    """;
    List<String> conditions = [];
    conditions.add("STRFTIME('%H',a.`time`)=?");
    conditions.add("b.`radio_id`=$radioId");
    if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
      conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`lost_c0` ASC, a.`snr_c0` DESC";
    List<int> resultIds = [];
    List<String> hourList = List<String>.generate(24, (index) => index >= 10 ? '$index' : '0$index');
    for (String hour in hourList) {
      resultIds.addAll((await db.db.rawQuery(sql, [hour])).map((e) => int.tryParse(e['id']?.toString() ?? '')).toList().whereNotNull());
    }
    resultIds = resultIds.distinct((result, current) => result.contains(current));
    if (resultIds.isEmpty) {
      return null;
    } else {
      DateTime now = DateTime.now();
      String name = "P${now.millisecondsSinceEpoch}", time = Global.parseTime(now)!;
      await db.insert(this.name, DetectTaskBestEntity(TaskType.pick.name, name, radioId, startTimeFormat, endTimeFormat, time, resultIds.join(',')).toJson());
      return name;
    }
  }

  Future<int> delete(int id) async {
    try {
      return await db.delete(name, "`id` = ?", [id]);
    } on DatabaseException catch (e) {
      if (e.isForeignKeyConstraintError()) {
        ToastUtil.error("该项数据已经使用，请先删除与之关联的数据！");
      } else {
        ToastUtil.error("删除数据异常");
      }
      rethrow;
    }
  }

  Future<List<GeneralTaskResultEntity>> generalResult(int bestId, [bool currentHour = false]) async {
    String sql = """
    SELECT b.`id`,b.`detect_task_id`,b.`time` `as_time`,b.`channel`,b.`signal`,b.`noise`,b.`doppler`,b.`multi_path`,b.`hz`,
    c.`type`,c.`radio_id`,d.`name` `radio_name`,c.`name`,c.`role`,c.`time`,c.`disabled_mhz_id`,e.`values` `disabled_mhz_values`,c.`state`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("GeneralTaskResult")} b ON ','||a.`result_ids`||',' LIKE '%,'||CAST(b.`id` as TEXT)||',%'
    INNER JOIN ${tableName("DetectTask")} c ON c.`id`=b.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} d ON d.`id`=c.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} e ON e.`id`=c.`disabled_mhz_id`
    """;
    List<String> conditions = [];
    conditions.addAll(["a.`id`=$bestId", "a.`type`='${TaskType.general.name}'"]);
    if (currentHour) {
      int hour = DateTime.now().hour;
      conditions.add("STRFTIME('%H',b.`time`)='${hour >= 10 ? hour : '0$hour'}'");
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY STRFTIME('%H',b.`time`) ASC,b.`signal` DESC";
    return (await db.db.rawQuery(sql)).map((e) {
      GeneralTaskResultEntity entity = GeneralTaskResultEntity.fromJson(e);
      entity.detectTask?.id = entity.detectTaskId;
      return entity;
    }).toList();
  }

  Future<List<PickTaskResultEntity>> pickResult(int bestId, [bool currentHour = false]) async {
    String sql = """
    SELECT b.`id`,b.`detect_task_id`,b.`time` `as_time`,b.`hz`,b.`sync_c0`,b.`lost_c0`,b.`sync_c1`,b.`lost_c1`,b.`sync_c3`,b.`lost_c3`,b.`snr_lora`,b.`snr_c0`,b.`snr_c1`,b.`snr_c3`,
    c.`type`,c.`radio_id`,d.`name` `radio_name`,c.`name`,c.`role`,c.`time`,c.`disabled_mhz_id`,e.`values` `disabled_mhz_values`,c.`state`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("PickTaskResult")} b ON ','||a.`result_ids`||',' LIKE '%,'||CAST(b.`id` as TEXT)||',%'
    INNER JOIN ${tableName("DetectTask")} c ON c.`id`=b.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} d ON d.`id`=c.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} e ON e.`id`=c.`disabled_mhz_id`
    """;
    List<String> conditions = [];
    conditions.addAll(["a.`id`=$bestId", "a.`type`='${TaskType.pick.name}'"]);
    if (currentHour) {
      int hour = DateTime.now().hour;
      conditions.add("STRFTIME('%H',b.`time`)='${hour >= 10 ? hour : '0$hour'}'");
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY STRFTIME('%H',b.`time`) ASC,b.`lost_c0` ASC,b.`snr_c0` DESC";
    return (await db.db.rawQuery(sql)).map((e) {
      PickTaskResultEntity entity = PickTaskResultEntity.fromJson(e);
      entity.detectTask?.id = entity.detectTaskId;
      return entity;
    }).toList();
  }

  Future<List<DetectTaskBestEntity>> list(TaskType type, [int? radioId, DateTime? startTime, DateTime? endTime]) async {
    String sql = """
    SELECT a.`id`,a.`type`,a.`radio_id`,a.`name`,b.`name` `radio_name`,a.`start_time`,a.`end_time`,a.`time`,a.`result_ids`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("Radio")} b ON b.`id`=a.`radio_id`
    """;
    List<String> conditions = ["a.`type`='${type.name}'"];
    if (radioId != null) conditions.add("a.`radio_id`=$radioId");
    if (startTime != null && endTime != null) {
      String? startTimeFormat = Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)), endTimeFormat = Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59));
      if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
        conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
      }
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`time` DESC";
    return (await db.db.rawQuery(sql)).map((e) => DetectTaskBestEntity.fromJson(e)).toList();
  }
}

class GeneralTaskHoursApi extends DBApi {
  GeneralTaskHoursApi(super.name);

  Future<int> insert(List<GeneralTaskHoursEntity> list) => db.insertBatch(name, list.map((e) => e.toJson()).toList());

  Future<int> update(GeneralTaskHoursEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<GeneralTaskHoursEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : GeneralTaskHoursEntity.fromJson(result.single);
  }

  Future<List<GeneralTaskHoursEntity>> list([String? where, List<dynamic>? args]) async =>
      (await db.select(name, orderBy: "`hour` ASC", where: where, args: args)).map((e) => GeneralTaskHoursEntity.fromJson(e)).toList();
}

class GeneralTaskResultApi extends DBApi {
  GeneralTaskResultApi(super.name);

  Future<int> insert(GeneralTaskResultEntity entity) => db.insert(name, entity.toJson());

  Future<int> delete(int id) => db.delete(name, "`id` = ?", [id]);

  Future<int> update(GeneralTaskResultEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<GeneralTaskResultEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : GeneralTaskResultEntity.fromJson(result.single);
  }

  Future<List<GeneralTaskResultEntity>> list({int? detectTaskId, int? radioId, DateTime? startTime, DateTime? endTime}) async {
    String sql = """
    SELECT a.`id`,a.`detect_task_id`,a.`time` `as_time`,a.`channel`,a.`signal`,a.`noise`,a.`doppler`,a.`multi_path`,a.`hz`,
    b.`radio_id`,c.`name` `radio_name`,b.`type`,b.`role`,b.`name`,b.`time`,b.`disabled_mhz_id`,d.`values` `disabled_mhz_values`,b.`state`
    FROM ${tableName()} a
    INNER JOIN ${tableName("DetectTask")} b ON b.`id`=a.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=b.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} d ON d.`id`=b.`disabled_mhz_id`
    """;
    List<String> conditions = [];
    if (detectTaskId != null) conditions.add("a.`detect_task_id`=$detectTaskId");
    if (radioId != null) conditions.add("b.`radio_id`=$radioId");
    if (startTime != null && endTime != null) {
      String? startTimeFormat = Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)), endTimeFormat = Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59));
      if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
        conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
      }
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`time` DESC,a.`signal` DESC";
    return (await db.db.rawQuery(sql)).map((e) {
      GeneralTaskResultEntity generalTaskResultEntity = GeneralTaskResultEntity.fromJson(e);
      generalTaskResultEntity.detectTask?.id = generalTaskResultEntity.detectTaskId;
      return generalTaskResultEntity;
    }).toList();
  }
}

class PickTaskHoursApi extends DBApi {
  PickTaskHoursApi(super.name);

  Future<int> insert(int detectTaskId, String modemType, int power, String strategy, String resultIds) async {
    List<PickTaskHoursEntity> entities = [];
    for (int h = 0; h < 24; h++) {
      List<int> hzs = (await db.db.query(tableName("GeneralTaskResult"), where: "',$resultIds,' LIKE '%,'||CAST(`id` as TEXT)||',%' AND STRFTIME('%H',`time`)='${h >= 10 ? h : '0$h'}'"))
          .map<int?>((e) => int.parse(e['hz']!.toString()))
          .toList()
          .whereNotNull();
      entities.add(PickTaskHoursEntity(detectTaskId, h, power, modemType, strategy, hzs.join(',')));
    }
    return db.insertBatch(name, entities.map((e) => e.toJson()).toList());
  }

  Future<int> update(PickTaskHoursEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<PickTaskHoursEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : PickTaskHoursEntity.fromJson(result.single);
  }

  Future<List<PickTaskHoursEntity>> list([String? where, List<dynamic>? args]) async =>
      (await db.select(name, orderBy: "`hour` ASC", where: where, args: args)).map((e) => PickTaskHoursEntity.fromJson(e)).toList();
}

class PickTaskResultApi extends DBApi {
  PickTaskResultApi(super.name);

  Future<int?> insert(PickTaskResultEntity entity) async {
    try {
      if (await db.insert(name, entity.toJson(), []) >= 1) {
        List<Map<String, dynamic>> result = await db.select(name, columns: ['id'], orderBy: "`id` DESC", limit: 1);
        return result.isEmpty ? null : result.single['id'];
      } else {
        return null;
      }
    } catch (e) {
      ToastUtil.error("数据创建异常 => ${e.toString()}");
      rethrow;
    }
  }

  Future<int> delete(int id) => db.delete(name, "`id` = ?", [id]);

  Future<int> update(PickTaskResultEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<PickTaskResultEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : PickTaskResultEntity.fromJson(result.single);
  }

  Future<List<PickTaskResultEntity>> list({int? detectTaskId, int? radioId, DateTime? startTime, DateTime? endTime}) async {
    String sql = """
    SELECT a.`id`,a.`detect_task_id`,a.`time` `as_time`,a.`hz`,a.`sync_c0`,a.`lost_c0`,a.`sync_c1`,a.`lost_c1`,a.`sync_c3`,a.`lost_c3`,a.`snr_lora`,a.`snr_c0`,a.`snr_c1`,a.`snr_c3`,
    b.`radio_id`,c.`name` `radio_name`,b.`type`,b.`role`,b.`name`,b.`time`,b.`disabled_mhz_id`,d.`values` `disabled_mhz_values`,b.`state`
    FROM ${tableName()} a
    INNER JOIN ${tableName("DetectTask")} b ON b.`id`=a.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=b.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} d ON d.`id`=b.`disabled_mhz_id`
    """;
    List<String> conditions = [];
    if (detectTaskId != null) conditions.add("a.`detect_task_id`=$detectTaskId");
    if (radioId != null) conditions.add("b.`radio_id`=$radioId");
    if (startTime != null && endTime != null) {
      String? startTimeFormat = Global.parseTime(startTime.copyWith(hour: 0, minute: 0, second: 0)), endTimeFormat = Global.parseTime(endTime.copyWith(hour: 23, minute: 59, second: 59));
      if (startTimeFormat != null && endTimeFormat != null && startTimeFormat.isNotEmpty && endTimeFormat.isNotEmpty) {
        conditions.add("a.`time` BETWEEN '$startTimeFormat' AND '$endTimeFormat'");
      }
    }
    if (conditions.isNotEmpty) sql += "WHERE ${conditions.join(" AND ")} ";
    sql += "ORDER BY a.`time` DESC,a.`lost_c0` ASC,a.`snr_c0` DESC";
    return (await db.db.rawQuery(sql)).map((e) {
      PickTaskResultEntity pickTaskHzEntity = PickTaskResultEntity.fromJson(e);
      pickTaskHzEntity.detectTask?.id = pickTaskHzEntity.detectTaskId;
      return pickTaskHzEntity;
    }).toList();
  }
}

class PickTaskResultPackageApi extends DBApi {
  PickTaskResultPackageApi(super.name);

  Future<int> insert(PickTaskResultPackageEntity entity) => db.insert(name, entity.toJson());

  Future<int> delete(int id) => db.delete(name, "`id` = ?", [id]);

  Future<int> update(PickTaskResultPackageEntity entity) => db.update(name, entity.toJson(), "`id` = ?", [entity.id]);

  Future<int> setResultId(int resultId, DateTime dateTime, int hz) {
    String startTime = Global.parseTime(dateTime.add(const Duration(minutes: -6)))!, endTime = Global.parseTime(dateTime)!;
    return db.update(name, {"result_id": resultId}, "`result_id` IS NULL AND `hz`=? AND STRFTIME('%Y-%m-%d %H:%M:%S',`time`) BETWEEN ? AND ?", [hz, startTime, endTime]);
  }

  Future<PickTaskResultPackageEntity?> select(int id) async {
    List<Map<String, dynamic>> result = await db.select(name, where: "`id` = ?", args: [id]);
    return result.isEmpty ? null : PickTaskResultPackageEntity.fromJson(result.single);
  }

  Future<List<PickTaskResultPackageEntity>> list(int resultId) async {
    String sql = """
    SELECT a.`id`,a.`detect_task_id`,a.`result_id`,a.`time` `as_time`,a.`data`,a.`channel`,a.`modem_type`,a.`hz`,
    b.`radio_id`,c.`name` `radio_name`,b.`type`,b.`role`,b.`name`,b.`time`,b.`disabled_mhz_id`,d.`values` `disabled_mhz_values`,b.`state`
    FROM ${tableName()} a
    INNER JOIN ${tableName("DetectTask")} b ON b.`id`=a.`detect_task_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=b.`radio_id`
    LEFT JOIN ${tableName("DisabledMHz")} d ON d.`id`=b.`disabled_mhz_id`
    WHERE a.`result_id`=$resultId
    ORDER BY a.`time` DESC
    """;
    return (await db.db.rawQuery(sql)).map((e) {
      PickTaskResultPackageEntity pickTaskResultEntity = PickTaskResultPackageEntity.fromJson(e);
      pickTaskResultEntity.detectTask?.id = pickTaskResultEntity.detectTaskId;
      return pickTaskResultEntity;
    }).toList();
  }
}

class ChatApi extends DBApi {
  ChatApi(super.name);

  Future<ChatEntity?> insert(ChatEntity entity) async {
    if (await db.insert(name, entity.toJson()) >= 1) {
      List<Map<String, dynamic>> result = await db.select(name, orderBy: "`time` DESC", limit: 1);
      return result.isEmpty ? null : ChatEntity.fromJson(result.single);
    } else {
      return null;
    }
  }

  Future<List<ChatEntity>> list(int localId, int targetId) async {
    String sql = """
    SELECT a.`id`,a.`sender_id`,b.`name` `sender_name`,a.`receiver_id`,c.`name` `receiver_name`,a.`content`,a.`content_type`,a.`time`
    FROM ${tableName()} a
    LEFT JOIN ${tableName("Radio")} b ON b.`id`=a.`sender_id`
    LEFT JOIN ${tableName("Radio")} c ON c.`id`=a.`receiver_id`
    WHERE (a.`sender_id`=$localId AND a.`receiver_id`=$targetId) OR (a.`sender_id`=$targetId AND a.`receiver_id`=$localId)
    ORDER BY a.`time` DESC
    """;
    return (await db.db.rawQuery(sql)).map((e) => ChatEntity.fromJson(e)).toList();
  }
}
